<?php
require_once("../../config.inc.php");
$isReloadable = true;
require_once(SERVER_FS_ROOT."/app/_inc/include.php");
require_once(SERVER_FS_ROOT."/app/util/imex/_inc/importExport.func.php");
require_once(SERVER_FS_ROOT."/app/util/imex/_inc/aRefIntegr.const.php");
require_once(SERVER_FS_ROOT."/app/util/imex/_inc/aStructMB.const.php");

$errMsg = sxREQ('msg');

$objUser = $_SESSION[USEROBJ];

if (sxREQ('reqtype') == 'keydel') {
	foreach($_REQUEST as $nom => $val) {
		if (substr($nom,0,4) != 'cbx_') continue;
		$aKD = explode('@/@',sxREQ($nom));
		$sSQL = "DELETE FROM ".$aKD[0]." WHERE ";
		$sep = '';
		for($k=1; $k<count($aKD); $k++) {
			$aX = explode('@=@',$aKD[$k]);
			$sSQL .= $sep.$aX[0]." = '".addslashes($aX[1])."'";
			$sep = ' AND ';
		}
		//dbg_($sSQL);
		$objSDAO->exec($sSQL);
	}
}

$aTestRes = refIntegrCheck();

$aOpz = array("kMneu"=>"uti.ick", "isReloadable"=>$isReloadable);
echo(headerHTML($aOpz));
?>
<script language="JavaScript" type="text/javascript">
function keyDelIC() {
	document.objFrm.reqtype.value = 'keydel';
	document.objFrm.submit();
}
function reloadIC() {
	document.objFrm.reqtype.value = 'reload';
	document.objFrm.submit();
}
function setCbxIC() {
	for(k=1;k<1000;k++) {
		oCbx = document.getElementById('cbx_'+String(k));
		if (!oCbx) { break; }
		oCbx.checked = document.objFrm.set_cbx.checked; 
	}
}
</script>
  
<?php
echo(bodyHTML($aOpz));
echo("<table width=\"100%\" cellpadding=\"03\" cellspacing=\"0\">\n<tr>\n<td>\n");
//output comandi
$aCmd = array(
	"kdel"=>array("pag"=>"javascript:keyDelIC()",
						"title"=>"delete selected keys",
						"text"=>"delete selected keys"
						),
	"proc"=>array("pag"=>"javascript:reloadIC()",
						"title"=>"process",
						"text"=>"process"
						),
	"back"=>array()
	);
echo(commandsHTML($aCmd));	
echo("<table class=\"tabl01\" width=\"100%\" cellpadding=\"3\" cellspacing=\"2\" border='0'>\n");
echo "<form name='objFrm' method='post' action='".$_SERVER['PHP_SELF']."'>\n";
echo "<input type='hidden' name='xsn' value='".XSN."'>\n";
echo "<input type='hidden' name='reqtype' value='reload'>\n";
echo("<tr>" .
			"<td class=\"titl01\" colspan='6'>Referential integrity check</td>" .
		"</tr>\n");
if (count($aTestRes) == 0) {
echo("<tr>" .
			"<td class=\"text01\" colspan='6'>All OK !</td>" .
		"</tr>\n");
} else {
	echo "<tr>\n";
	echo "<td class=\"tabh01\" width='20%'>Parent entity</td>\n";
	echo "<td class=\"tabh01\" width='19%'>Child entity</td>\n";
	echo "<td class=\"tabh01\" width='1%'>" .
			"<input type='checkbox' name='set_cbx' value='x' class='tabh01' " .
			" onClick='setCbxIC();'>".
			"</td>\n";
	echo "<td class=\"tabh01\" width='20%'>primary key</td>\n";
	echo "<td class=\"tabh01\" width='1%'>rel.</td>\n";
	echo "<td class=\"tabh01\" width='39%'>chiled key >>> missing parent key</td>\n";
	echo "</tr>\n";
	$nn = 0;
	foreach($aTestRes as $par => $aPar) {
		foreach($aPar as $chi => $aChi) {
			foreach($aChi as $rel => $aRel) {
				if (count($aRel) == 0) {
						echo "<tr>\n";
						echo "<td class=\"text01\">".$par."</td>\n";
						echo "<td class=\"text01\">".$chi."</td>\n";
						echo "<td class=\"text01\">&nbsp;</td>\n";
						echo "<td class=\"text01\">&nbsp;</td>\n";
						echo "<td class=\"text01\">".$rel."</td>\n";
						echo "<td class=\"text01\">-- OK --</td>\n";
						echo "</tr>\n";
				} else {
					foreach($aRel as $zz => $sCampi) {
						$nn++;
						echo "<tr>\n";
						echo "<td class=\"text01\" valign='top'>".$par."</td>\n";
						echo "<td class=\"text01\" valign='top'>$chi</td>\n";
						echo "<td class=\"text01\" valign='top'>" .
								"<input type='checkbox' name='cbx_$nn' id='cbx_$nn' value=\"".htmlentities($sCampi[2])."\" class='text01'" .
								"</td>\n";
						echo "<td class=\"text01\" valign='top'>".$sCampi[0]."</td>\n";
						echo "<td class=\"text01\" valign='top'>".$rel."</td>\n";
						echo "<td class=\"text01\" valign='top'>".$sCampi[1]."</td>\n";
						echo "</tr>\n";
					}
				}
			}
		}
	}
}
echo("</form>\n");
echo("</table>\n");
echo(commandsHTML($aCmd));
echo("</td>\n</tr>\n</table>\n");
echo(footerHTML());
exit();

function refIntegrCheck () {
	global $objSDAO, $aRefIntegr, $aStructMB;
	$aTestRes = array();
	foreach($aRefIntegr as $par => $aPar) {
		foreach($aPar['chi'] as $chi => $aRel) {
			foreach($aRel as $nn => $rel) {
				//$aTestRes[$par][$chi][$nn] = array();
				if (isset($rel['key'])) {
					$aKC = $rel['key'];
				} else {
					$aKC = $aPar['key'];
				}
				$sCondJoin = $sListKey = '';
				$sepJ = $sepL = $sepW = '';
				$sWhere = '';
				foreach($aKC as $z => $key) {
					$sCondJoin .= $sepJ.'c.'.$key.' = p.'.$aPar['key'][$z];
					$sepJ = ' AND ';
					$sListKey .= $sepL.'c.'.$key.' AS FK_'.$key;
					$sepL = ', ';
					foreach($aStructMB[$chi] as $xx => $pk) {
						$sListKey .= $sepL.'c.'.$pk.' AS PK_'.$pk;
					}
					$sWhere .= $sepW.'ISNULL(p.'.$aPar['key'][$z].')';
					$sepW = ' AND ';
					if ($rel['opz'] == 'Y') { $sWhere .= ' AND '.'c.'.$key." <> ''"; }
				}
				$sSQL = "SELECT $sListKey FROM $chi c LEFT JOIN $par p ON $sCondJoin WHERE $sWhere";
				$objSDAO->query($sSQL);
				while($row = $objSDAO->fetch()) {
					$sCampiK = $sepK = "";
					$sCampiF = $sepF = "";
					$sKeyDel = "$chi";
					$zz = 0;
					foreach($row as $nom => $val) {
						if (substr($nom,0,3) == 'FK_') {
							$sCampiF .= $sepF.substr($nom,3).' >>> '.$aPar['key'][$zz++]."=".$val;
							$sepF="<br>";
						} else {
							$sCampiK .= $sepK.substr($nom,3)."=".$val;
							$sepK="<br>";
							$sKeyDel .= "@/@".substr($nom,3).'@=@'.$val;
						}
					}
					$aTestRes[$par][$chi][$nn][] = array($sCampiK,$sCampiF,$sKeyDel);
				}
			}
		}
	}
	return $aTestRes;
} 
